# -*- coding:utf-8 -*-

"""
class ProjectSql(object):
    # 查看项目列表
    QUERY_PROJECT_LIST = "SELECT  project_name, project.id, ui.user_name, create_time " \
                         "FROM project " \
                         "LEFT JOIN user_info ui " \
                         "ON project.user_id = ui.id "

    QUERY_PROJECT_LIST_END = "ORDER BY create_time desc"

    ADD_PROJECT_SQL = "INSERT INTO project(project_name, user_id, create_time) " \
                      "VALUES(%s, %s, %s) "

    ALL_TASK = "SELECT task.id, task_name, project_name,user_name partner_name, ui.id partner_id, level, " \
               "actual_hour, plan_start_time, " \
               "start_time, end_time, journal, task.create_time, is_start, plan_end_time FROM task " \
               "LEFT JOIN project " \
               "ON task.project_id = project.id " \
               "LEFT JOIN user_info ui " \
               "ON ui.id = task.user_id " \
               "WHERE 1 = 1 "

    ALL_TASK_END = "ORDER BY create_time DESC "

    SHOW_ALL_PARTNER = "SELECT *  FROM partner WHERE is_delete = 1"

    ADD_TASK = "INSERT INTO task(task_name, project_id, journal, user_id, plan_start_time,plan_end_time, partner_id, " \
               "create_time, is_start) " \
               "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"

    START_TASK = "UPDATE task SET start_time = %s, is_start = %s WHERE id = %s "

    END_TASK = "UPDATE task SET end_time = %s, is_start = %s WHERE id = %s "

    PROJECT = "SELECT id, project_name FROM project "

    PARTNER = "SELECT id, user_name partner_name  FROM user_info "

    ADD_PARTBER = "INSERT INTO partner(partner_name, work_number) VALUES(%s, %s)"

    ADD_JOURNAL = "INSERT INTO journal(work_progress, user_id, task_id, coordination_work, experience, creat_time) " \
                  "VALUES(%s, %s, %s, %s, %s, %s) "

    ALL_JOURNAL = "SELECT work_progress, journal.creat_time, coordination_work, experience, ui.user_name, task_name  " \
                  "FROM journal " \
                  "LEFT JOIN user_info ui " \
                  "ON journal.user_id = ui.id " \
                  "LEFT JOIN task " \
                  "ON task.id = journal.task_id " \
                  "WHERE 1 = 1 "

    ALL_JOURNAL_END = "ORDER BY creat_time DESC "
"""


class ProjectSql(object):
    # 添加项目
    ADD_PROJECT = "INSERT INTO project(name, leader, status, " \
                  "create_date, end_date,plane_time,remarks,process) VALUES (%s,%s, %s,%s, %s, %s, %s,0)returning id"
    
    # 删除项目
    DEL_PROJECT = "DELETE FROM project WHERE id=%s;"
    
    # 编辑项目
    EDIT_PROJECT = "UPDATE project SET name=%s, leader=%s, status=%s, " \
                   "create_date=%s, plane_time=%s, end_date=%s,remarks=%s,process=%s WHERE id=%s;"
    
    # 项目完成
    OVER_PROJECT = "UPDATE project SET name=%s, leader=%s, status=%s, " \
                   "create_date=%s, plane_time=%s, end_date=%s,process=%s WHERE id=%s; " \
                   "DELETE FROM group_project WHERE object_id=%s; " \
                   "DELETE FROM group_task WHERE project_id=%s; "
    
    # 项目列表
    PROJECT_LIST = "SELECT p.id, p.name, u.user_name as \"leader\", p.status, p.create_date, " \
                   "p.plane_time, p.end_date,p.process " \
                   "FROM project as p " \
                   "INNER JOIN user_info as u ON u.id=p.leader"
    
    # 项目搜索
    PROJECT_SEARCH = "SELECT p.id, p.name, u.user_name as \"leader\", p.status, p.create_date, " \
                     "p.plane_time, p.end_date,p.process " \
                     "FROM project as p " \
                     "INNER JOIN user_info as u ON u.id=p.leader WHERE p.name LIKE " \
                      "'%%'||%s||'%%' or p.remarks LIKE '%%'||%s||'%%' or u.user_name LIKE '%%'||%s||'%%'"
    
    # 项目详情
    PROJECT_INFO = "SELECT p.id,p.remarks, p.name, u.user_name as \"leader\", p.status, p.create_date, " \
                   "p.plane_time, p.end_date,p.process " \
                   "FROM project as p " \
                   "INNER JOIN user_info as u ON u.id=p.leader WHERE p.id=%s"
    
    # 添加项目团队
    ADD_PROJECT_GROUP = "INSERT INTO group_project(object_id, remarks,user_id) VALUES (%s,%s,%s)returning id"
    
    # 编辑项目团队
    EDIT_PROJECT_GROUP = "UPDATE group_project SET object_id=%s, remarks=%s,user_id=%s WHERE id=%s;"
    
    # 删除项目团队
    DEL_PROJECT_GROUP = " DELETE FROM group_project WHERE object_id=%s;"
    
    # 项目团队列表
    PROJECT_GROUP_LIST = "SELECT gt.object_id, gt.remarks, u.user_name as \"name\"," \
                         "p.name,u.id as \"user_id\",u2.user_name FROM group_project as gt " \
                         "INNER JOIN user_info as u ON gt.user_id = u.id " \
                         "INNER JOIN project as p ON p.id=gt.object_id " \
                         "INNER JOIN user_info as u2 ON p.leader = u2.id "
    
    # 项目团队详情
    PROJECT_GROUP_INFO = "SELECT u.id,gt.id as \"gtid\",gt.object_id, gt.remarks , u.user_name as \"name\"," \
                         "p.name as \"project_name\",u.id as \"user_id\",u2.user_name FROM group_project as gt " \
                         "INNER JOIN user_info as u ON gt.user_id = u.id " \
                         "INNER JOIN project as p ON p.id=gt.object_id " \
                         "INNER JOIN user_info as u2 ON p.leader = u2.id " \
                         "WHERE gt.object_id = %s;"
    
    # 添加项目团队成员
    PROJECT_GROUP_EDIT_USER = "UPDATE group_project SET object_id=%s, remarks=%s,user_id=%s WHERE id=%s;"
    
    # 删除项目团队成员
    PROJECT_GROUP_DEL_USER = "DELETE FROM group_project WHERE user_id=%s and object_id=%s;DELETE FROM group_task WHERE user_id=%s and project_id=%s;"
    
    # 所有leader列表
    PROJECT_GROUP_LEADER_LIST = "SELECT id,user_name  FROM user_info WHERE company_post=2 "  # and user_status=0 and department=%s"
    
    # 根据用户权限返回项目信息
    PROJECT_INFO_PERSON = "SELECT p.id,p.name,p.remarks, gp.remarks as \"grm\",u.user_name as \"leader\", p.status,gp.user_id,p.create_date," \
                          "p.end_date,p.plane_time FROM group_project as gp " \
                          "INNER JOIN project as p ON gp.object_id=p.id " \
                          "INNER JOIN user_info as u ON u.id=p.leader Where (gp.user_id=%s or %s>2) and status<3"
    
    PROJECT_INFO_PERSON_ALL = "SELECT distinct p.id,p.name,p.remarks, u.user_name as \"leader\", " \
                              "p.status,p.create_date,p.end_date,p.plane_time " \
                              "FROM group_project as gp " \
                              "INNER JOIN project as p ON gp.object_id=p.id " \
                              "INNER JOIN user_info as u ON u.id=p.leader " \
                              "Where (gp.user_id=12 or 3=3) and status<3"
    
    PROJECT_EMPTY_PERSON = "SELECT * from user_info EXCEPT SELECT u.* FROM group_project as gp INNER JOIN user_info as u ON gp.user_id=u.id"
    
    PROJECT_EDIT_LEADER = "UPDATE project SET leader=%s WHERE id=%s"
    
    PROJECT_GROUP_LIST_PERSON = "select u.user_name,u.company_post from user_info as u " \
                                "INNER JOIN group_project as gp ON u.id=gp.user_id " \
                                "WHERE gp.object_id " \
                                "IN(select p.id from group_project as gp INNER JOIN project as p ON p.id=gp.object_id WHERE gp.user_id=%s or %s>2) ORDER BY u.company_post DESC "
    
    # 团队中删除成员同时要在任务成员中将其删除
    PROJECT_GROUP_DEL_TASK_GROUP = " DELETE FROM group_task WHERE id IN (SELECT gt.id FROM group_task as gt INNER JOIN task as t ON gt.task_id=t.id INNER JOIN project as p ON t.object_id=p.id WHERE gt.user_id=%s and p.id=%s);"
